package com.bt.lms.service.impl;

import com.bt.lms.constant.BOConstant;
import com.bt.lms.entity.Role;
import com.bt.lms.entity.User;
import com.bt.lms.entity.page.Page;
import com.bt.lms.dao.entity.RlUserRolePo;
import com.bt.lms.dao.servise.UserDao;
import com.bt.lms.dao.servise.impl.UserDaoImpl;
import com.bt.lms.service.UserService;

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

/**
 * 后台用户服务
 *
 * @author 徐大永
 */
public class UserServiceImpl implements UserService {

    @Override
    public Page<User> queryPage(Page<User> page, String vagueName) {
        //涉及三张表 用户本表 以及 角色表的名字列 外加关系表

        //存放用户的对象
        List<User> users = new ArrayList<>();
        page.setRecords(users);

        //dao对象
        UserDao userDao = new UserDaoImpl();

        //查询用户信息，用户本表   查询条件   开始位置   数量
        StringBuilder userSqlSB = new StringBuilder();
        userSqlSB.append("SELECT * FROM user WHERE name LIKE '%").append(vagueName)
                .append("%' AND deleted=").append(BOConstant.DELETED_FALSE).append(" LIMIT ?,?");
        String userSql = new String(userSqlSB);
        //查询总数
        StringBuilder userSumSqlSB = new StringBuilder();
        userSumSqlSB.append("SELECT COUNT(*) FROM user WHERE deleted=").append(BOConstant.DELETED_FALSE);
        String userSumSql = new String(userSumSqlSB);
        //dao方法 得全部用户基本信息（在集合中）
        userDao.query(userSql, page, users, userSumSql);

        //sql
        StringBuilder rlSqlSB = new StringBuilder();
        rlSqlSB.append("SELECT role_id FROM rl_user_role WHERE user_id_ = ? AND deleted=")
                .append(BOConstant.DELETED_FALSE);
        String rlSql = new String(rlSqlSB);
        //针对单个用户查询其拥有的角色名称
        for (User user : users) {

            //需要查询出的属性
            List<Role> roles = new ArrayList<>();
            user.setRoles(roles);

            //用户id
            Long userId = user.getId();
            //dao方法 的角色id
            List<Long> roleIdS = userDao.queryRl(rlSql, userId);

            //sql 角色表
            String roleSql = "SELECT name FROM role WHERE id=?";
            for (Long roleId : roleIdS) {
                //dao方法 得角色名称 + id
                Role role = userDao.queryRole(roleSql, roleId);
                roles.add(role);
            }
        }
        return page;
    }

    @Override
    public User detail(Long id) {

        //dao对象
        UserDao userDao = new UserDaoImpl();
        //关系几张表  用户本表(user) + 用户角色关系表（rl_user_role） +  角色信息表(role)
        String sql = "SELECT * FROM user WHERE id = ?";
        //调用dao
        User user = userDao.selectUser(sql, id);
        //对象的关系属性
        List<Role> roles = new ArrayList<>();
        user.setRoles(roles);

        //用户角色关系表
        String rlUserRole = "SELECT role_id,install_role_time FROM rl_user_role WHERE user_id_ = ?";
        //调用dao 得用户角色所有关系PO
        List<RlUserRolePo> rlUserRolePos = userDao.selectRl(rlUserRole, id);
        //循环得到所有关联角色
        for (RlUserRolePo rlUserRolePo : rlUserRolePos) {
            //角色信息表
            String roleSql = "SELECT code,name FROM role WHERE id = ?";
            Long roleId = rlUserRolePo.getRoleId();
            //调用dao 得角色对象
            Role role = userDao.select(roleSql, roleId);
            //维护关联时间
            LocalDateTime installRoleTime = rlUserRolePo.getInstallRoleTime();
            role.setInstallRoleTime(installRoleTime);
            roles.add(role);
        }
        return user;
    }

    @Override
    public boolean add(User user) {

        //关于表，和几张表有关系 user *----* role (rl_user_role)  （用户信息 + 角色权限信息）
        //1.需要系统维护那些属性（不需要用户输入的属性）
        user.setCreatedTime(LocalDateTime.now());//创建时间
        user.setCreatedName("张三");//临时的创建人
        user.setStatus(BOConstant.USER_STATUS_STOP);//状态
        user.setPassword(BOConstant.USER_PASSWORD_DEFAULT);//初始密码
        user.setDeleted(BOConstant.DELETED_FALSE);//是否删除

        //dao对象
        UserDao userDao = new UserDaoImpl();

        //要执行的SQL(用户本表
        StringBuilder sqlSB = new StringBuilder();
        sqlSB.append("INSERT INTO user (account_number,name,code_type,code,unit,mobile_phone,created_name,created_time,status,password,deleted)")
                .append("VALUES (?,?,?,?,?,?,?,?,?,?,?)");
        String sql = new String(sqlSB);
        //调用dao 返回添加成功的id
        Long userId = userDao.insert(sql, user);

        //2.2 用户角色关系表
        //判断是否关联了角色
        List<Role> roles = user.getRoles();
        if (roles != null) {
            int size = roles.size();
            if (size == 0) {//没有关联角色
                return true;
            } else {
                //拥有关联的角色  批量插入
                //组装SQL（用户角色关系
                String rlSql = "INSERT INTO rl_user_role (user_id_,role_id,install_role_time) VALUE (?,?,?)";
                //准备dao方法 参数
                List<Long> roleIds = new ArrayList<>();
                for (Role role : roles) {
                    Long roleId = role.getId();
                    roleIds.add(roleId);
                }
                //掉dao 方法
                int i = userDao.insertRlS(rlSql, roleIds, userId);
                return i == size;
            }
        }
        return true;
    }

    @Override
    public boolean edit(User user) {

        //dao对象
        UserDao userDao = new UserDaoImpl();

        //SQL  用户 UPDATE user SET name = '咏柳' WHERE id = 23;
        String userSql = "UPDATE user SET account_number=?,name=?,code_type=?,code=?,unit=?,mobile_phone=? WHERE id = ?";
        //调用dao
        int i = userDao.update(userSql, user);

        return i == 1;
    }

    @Override
    public boolean pause(Long id) {

        //修改用户 状态
        //dao对象
        UserDao userDao = new UserDaoImpl();

        //SQL  用户 UPDATE user SET status=? WHERE id=?;
        String sql = "UPDATE user SET status=? WHERE id=?";
        //dao方法 所需参数
        int i = userDao.update(sql, id, BOConstant.USER_STATUS_STOP);

        return i == 1;
    }

    @Override
    public boolean enable(Long id) {

        //修改用户 状态
        //dao对象
        UserDao userDao = new UserDaoImpl();

        //SQL  用户 UPDATE user SET status=? WHERE id=?;
        String sql = "UPDATE user SET status=? WHERE id=?";
        //dao方法 所需参数
        int i = userDao.update(sql, id, BOConstant.USER_STATUS_START);

        return i == 1;
    }

    @Override
    public boolean del(Long id) {

        //dao 对象
        UserDao userDao = new UserDaoImpl();

        //SQL  删除 本表（状态置为已删除）
        StringBuilder rlSqlSB = new StringBuilder();
        rlSqlSB.append("UPDATE user SET deleted = ").append(BOConstant.DELETED_TRUE).append(" WHERE id = ?");
        String rlSql = new String(rlSqlSB);
        //调用dao 删除本表
        boolean b1 = userDao.deleteRl(rlSql, id);

        //删除关系表
        StringBuilder delRlSB = new StringBuilder();
        delRlSB.append("UPDATE rl_user_role SET deleted = ").append(BOConstant.DELETED_TRUE).append(" WHERE user_id_ = ?");
        String delRl = new String(delRlSB);
        //调用dao
        boolean b = userDao.deleteRl(delRl, id);

        return b && b1;
    }

    @Override
    public boolean resetPsw(Long id, String psw) {

        //修改用户 密码
        //dao对象
        UserDao userDao = new UserDaoImpl();

        //SQL  用户 UPDATE user SET password=? WHERE id=?;
        String sql = "UPDATE user SET password=? WHERE id=?";
        //dao方法 所需参数
        int i = userDao.update(sql, id, psw);

        return i == 1;
    }

    @Override
    public boolean addRoleUser(List<Long> userIds, Long roleId) {

        //要执行的sql
        String sql = "INSERT rl_user_role (user_id_,role_id,install_role_time) VALUES (?,?,?)";

        //dao 方法
        UserDao userDao = new UserDaoImpl();
        int i = userDao.insertRlS(sql, userIds, roleId);
        return i == userIds.size();
    }

    @Override
    public Page<User> queryUserFromRole(Page<User> userPage, Long roleId) {

        //dao对象
        UserDao userDao = new UserDaoImpl();
        //sql 关系表
        StringBuilder sqlSB = new StringBuilder();
        sqlSB.append("SELECT u.id,u.account_number,u.name,u.created_name,u.created_time,u.login_time,u.status")
                .append(" FROM user u,rl_user_role rl")
                .append(" WHERE rl.role_id = ? AND u.id = rl.user_id_ AND rl.deleted = ")
                .append(BOConstant.DELETED_FALSE)
                .append(" LIMIT ?,?");
        String sql = new String(sqlSB);
        //dao方法 得该角色的用户信息（对应页）
        List<User> users = userDao.select(sql, userPage, roleId);

        //sql 数据总数
        StringBuilder roleSumSB = new StringBuilder();
        roleSumSB.append("SELECT COUNT(id) FROM rl_user_role WHERE deleted=").append(BOConstant.DELETED_FALSE)
                .append(" AND role_id = ?");
        String roleSum = new String(roleSumSB);
        Long total = userDao.selectSum(roleSum, roleId);
        userPage.setTotal(total);

        //为每一个用户查询角色
        for (User user : users) {
            StringBuilder sqlRlSB = new StringBuilder();
            sqlRlSB.append("SELECT role.name").append(" FROM rl_user_role rl,role").append(" WHERE rl.user_id_ = ? AND rl.role_id = role.id");

            String sqlRl = new String(sqlRlSB);
            userDao.selectRole(sqlRl, user);
        }
        userPage.setRecords(users);
        return userPage;
    }

    @Override
    public Page<User> queryRoleNotUser(Page<User> userPage, Long roleId) {

        //dao对象
        UserDao userDao = new UserDaoImpl();
        //sql 查询所有没有被角色关系对用户
        StringBuilder sqlSB = new StringBuilder();
        sqlSB.append("SELECT DISTINCT u.id,u.account_number,u.name,u.created_name,u.created_time,u.login_time,u.status")
                .append(" FROM user u,rl_user_role rl")
                .append(" WHERE u.id != rl.user_id_ AND u.deleted = ").append(BOConstant.DELETED_FALSE)
                .append(" AND rl.deleted = ").append(BOConstant.DELETED_FALSE)
                .append(" AND u.id NOT IN ")
                .append(" (SELECT u.id FROM USER u,rl_user_role rl")
                .append(" WHERE u.id = rl.user_id_ AND rl.role_id = ?)")
                .append(" LIMIT ?,?");
        String sql = new String(sqlSB);
        //dao方法 得该角色的用户信息（对应页）
        List<User> users = userDao.select(sql, userPage, roleId);

        //sql 数据总数(角色关联的用户总数
        StringBuilder roleSumSB = new StringBuilder();
        roleSumSB.append("SELECT COUNT(id) FROM rl_user_role WHERE deleted=").append(BOConstant.DELETED_FALSE)
                .append(" AND role_id = ?");
        String roleSum = new String(roleSumSB);
        Long roleTotal = userDao.selectSum(roleSum, roleId);
        //sql 用户总数
        StringBuilder userSumSB = new StringBuilder();
        userSumSB.append("SELECT COUNT(id) FROM user WHERE deleted=").append(BOConstant.DELETED_FALSE);
        String userSum = new String(userSumSB);
        Long userTotal = userDao.selectSum(userSum);
        userPage.setTotal(userTotal - roleTotal);

        //为每一个用户查询角色
        for (User user : users) {
            StringBuilder sqlRlSB = new StringBuilder();
            sqlRlSB.append("SELECT role.name").append(" FROM rl_user_role rl,role").append(" WHERE rl.user_id_ = ? AND rl.role_id = role.id")
                    .append(" AND rl.deleted = ").append(BOConstant.DELETED_FALSE)
                    .append(" AND role.deleted = ").append(BOConstant.DELETED_FALSE);

            String sqlRl = new String(sqlRlSB);
            List<Role> roles = userDao.selectRole(sqlRl, user);
            user.setRoles(roles);
        }
        userPage.setRecords(users);
        return userPage;
    }
}
